package com.LostAndFound.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.LostAndFound.utils.DataSourceUtils;

public class FailureRecodListDao {
	QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
	// 得到记录总条数
	public int getTotalRecordForEasyUI(String cid, String whatFlag) throws SQLException  {
		// invalidflag = 0 表示记录失效, foundflag = 1 表示记录未被找到
		String sql = "select count(*) from article where invalidflag = 0 and foundflag = 1";
		Long total = 0L;
		// 拼接分类
		if("all".equals(cid) && "all".equals(whatFlag)) {
			sql = sql+" ";
			total = (Long) runner.query(sql, new ScalarHandler());
		}else if("all".equals(cid) && !"all".equals(whatFlag)){
			sql = sql + " and whatflag = ? ";
			total = (Long) runner.query(sql, new ScalarHandler(),whatFlag);
		}else if(!"all".equals(cid) && "all".equals(whatFlag)) {
			sql = sql + " and cid = ? ";
			total = (Long) runner.query(sql, new ScalarHandler(),cid);
		}else if(!"all".equals(cid) && !"all".equals(whatFlag)) {
			sql = sql + " and cid = ? and whatflag = ?";
			total = (Long) runner.query(sql, new ScalarHandler(),cid,whatFlag);
		}
		return total.intValue();
	}
	// 得到记录展示
	public List<Map<String, Object>> getRecodListForEasyUI(int startIndex, int pageSize, String cid, String whatFlag) throws SQLException {
		String sql = "select a.*,u.*,c.* from article a,user u,category c "
				+ " where foundflag = 1 and invalidflag = 0 "
				+ " and a.uid=u.uid and a.cid = c.cid ";
		List<Map<String, Object>> mapList = null;
		// 拼接分类
		if("all".equals(cid) && "all".equals(whatFlag)) {
			sql = sql+" order by a.issuetime desc limit ?,? ";
			mapList = runner.query(sql, new MapListHandler(),startIndex,pageSize);
		}else if("all".equals(cid) && !"all".equals(whatFlag)){
			sql = sql + " and whatflag = ? order by a.issuetime desc limit ?,? ";
			mapList = runner.query(sql, new MapListHandler(),whatFlag,startIndex,pageSize);
		}else if(!"all".equals(cid) && "all".equals(whatFlag)) {
			sql = sql + " and a.cid = ? order by a.issuetime desc limit ?,? ";
			mapList = runner.query(sql, new MapListHandler(),cid,startIndex,pageSize);
		}else if(!"all".equals(cid) && !"all".equals(whatFlag)) {
			sql = sql + " and a.cid = ? and whatflag = ? order by a.issuetime desc limit ?,? ";
			mapList = runner.query(sql, new MapListHandler(),cid,whatFlag,startIndex,pageSize);
		}
		return mapList;
	}
	public void handleDeleteFailureRecordForEasyUI(String aid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "delete from article where aid = ?";
		runner.update(sql, aid);
	}

}
